//文件处理脚本
//执行命令npx ts-node importToMongo.ts
import * as xlsx from 'xlsx';
import { MongoClient } from 'mongodb';

// Excel 文件路径
const filePath = 'C:\\Users\\86181\\Desktop\\企业风险评分结果_xgboost.xlsx';

// MongoDB 配置
const mongoUrl = 'mongodb://localhost:27017';
const dbName = 'risk_backend';
const collectionName = 'company_scores';

// 字段映射（Excel列名 -> MongoDB字段名）
const fieldMap: Record<string, string> = {
  "Revenue": "Revenue",//营业总收入(元)
  "TotalCost": "TotalCost",//营业总成本(元)
  "TotalComprehensiveIncome": "TotalComprehensiveIncome",//综合收益总额(元)
  "BasicEPS": "BasicEPS",//基本每股收益(元)
  "CreditLoss": "CreditLoss",//信用减值损失合计(元)
  "AssetLoss": "AssetLoss",//资产减值损失合计(元)
  "最新公司全称_LComNm": "LComNm",//最新公司全称
  "信息发布日期_InfoPubDt": "InfoPubDate",//信息发布日期
  "RiskScore": "RiskScore",//风险评分
  "RiskLevel": "RiskLevel",//风险等级
};

// Excel 日期解析函数
function parseExcelDate(value: any): Date | null {
  if (typeof value === 'number') {
    const excelEpoch = new Date(Date.UTC(1899, 11, 30));
    const days = Math.floor(value);
    const millis = Math.round((value - days) * 24 * 60 * 60 * 1000);
    return new Date(excelEpoch.getTime() + days * 24 * 60 * 60 * 1000 + millis);
  } else if (typeof value === 'string') {
    const parsed = new Date(value);
    return isNaN(parsed.getTime()) ? null : parsed;
  }
  return null;
}

async function importData() {
  try {
    console.log('📄 正在读取 Excel 文件...');
    const workbook = xlsx.readFile(filePath);
    const sheet = workbook.Sheets[workbook.SheetNames[0]];
    const rawData = xlsx.utils.sheet_to_json<Record<string, any>>(sheet);

    console.log(`📊 总共读取到 ${rawData.length} 条数据`);

    const client = new MongoClient(mongoUrl);
    await client.connect();
    const db = client.db(dbName);
    const collection = db.collection(collectionName);

    for (const row of rawData) {
      const newRow: Record<string, any> = {};

      for (const [originalKey, mappedKey] of Object.entries(fieldMap)) {
        let value = row[originalKey];

        if (originalKey === "信息发布日期_InfoPubDt") {
          newRow[mappedKey] = parseExcelDate(value);
        } else {
          newRow[mappedKey] = value ?? null;
        }
      }

      const { LComNm, InfoPubDate } = newRow;

      if (!LComNm || !InfoPubDate) {
        console.warn(`⚠️ 缺少主键字段，跳过一条记录：${JSON.stringify(newRow)}`);
        continue;
      }

      await collection.updateOne(
        { LComNm, InfoPubDate }, // 查询条件
        { $set: newRow },        // 更新内容
        { upsert: true }         // 不存在则插入
      );
    }

    await client.close();
    console.log('✅ 数据导入完成（支持 upsert 更新）');
  } catch (err) {
    console.error('❌ 出错了:', err);
  }
}

importData();
// 运行此脚本前，请确保 MongoDB 服务已启动，并且数据库和集合已创建。
